﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;

namespace AccessControl.DataAcess
{
    public class FingerTemplateAccess
    {
        public static AccessControlDS.tblFingerTemplateDataTable SelectAll()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblFingerTemplate ", cn);
                var tblFingerTemplateTable = new AccessControlDS.tblFingerTemplateDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblFingerTemplateTable.NewtblFingerTemplateRow();
                        newRow.fkPerson = (String)reader["fkPerson"];
                        newRow.Template = (byte[])reader["Template"];
                        newRow.UpdateDate = (DateTime)reader["UpdateDate"];
                        if (reader["TemplateFinger"]!=DBNull.Value) newRow.TemplateFinger = (byte)reader["TemplateFinger"];
                        newRow.TemplateHash = (int)reader["TemplateHash"];
                        newRow.TemplateSize = (int)reader["TemplateSize"];
                        newRow.TemplateG = (int)reader["TemplateG"];
                        tblFingerTemplateTable.AddtblFingerTemplateRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblFingerTemplateTable.Rows.Count != 0 ? tblFingerTemplateTable : null;
            }
        }

        public static AccessControlDS.tblFingerTemplateDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblFingerTemplate WHERE {0}", filter), cn);
                var tblFingerTemplateTable = new AccessControlDS.tblFingerTemplateDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblFingerTemplateTable.NewtblFingerTemplateRow();
                        newRow.fkPerson = (String)reader["fkPerson"];
                        newRow.Template = (byte[])reader["Template"];
                        newRow.UpdateDate = (DateTime)reader["UpdateDate"];
                        if (reader["TemplateFinger"] != DBNull.Value) newRow.TemplateFinger = (byte)reader["TemplateFinger"];
                        newRow.TemplateHash = (int)reader["TemplateHash"];
                        newRow.TemplateSize = (int)reader["TemplateSize"];
                        newRow.TemplateG = (int)reader["TemplateG"];
                        tblFingerTemplateTable.AddtblFingerTemplateRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblFingerTemplateTable.Rows.Count != 0 ? tblFingerTemplateTable : null;
            }
        }

        public static void Insert(AccessControlDS.tblFingerTemplateRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spFingerTemplate_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdInsert.Parameters.AddWithValue("@TemplateFinger", row.TemplateFinger);
                cmdInsert.Parameters.AddWithValue("@Template", row.Template);
                cmdInsert.Parameters.AddWithValue("@TemplateSize", row.TemplateSize);
                cmdInsert.Parameters.AddWithValue("@TemplateHash", row.TemplateHash);
                cmdInsert.Parameters.AddWithValue("@TemplateG", row.TemplateG);
                cmdInsert.Parameters.AddWithValue("@UpdateDate", row.UpdateDate);
                try
                {
                    cn.Open();
                    cmdInsert.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblFingerTemplateRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spFingerTemplate_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdUpdate.Parameters.AddWithValue("@TemplateFinger", row.TemplateFinger);
                cmdUpdate.Parameters.AddWithValue("@Template", row.Template);
                cmdUpdate.Parameters.AddWithValue("@TemplateSize", row.TemplateSize);
                cmdUpdate.Parameters.AddWithValue("@TemplateHash", row.TemplateHash);
                cmdUpdate.Parameters.AddWithValue("@TemplateG", row.TemplateG);
                cmdUpdate.Parameters.AddWithValue("@UpdateDate", row.UpdateDate);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(AccessControlDS.tblFingerTemplateRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spFingerTemplate_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

    }
}
